package dao.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import model.Adresse;
import dao.jdbc.JdbcDao.JdbcDAO;

public class AdresseJdbcDao implements JdbcDAO<Adresse> {

  private PreparedStatement updateStatement = null;
  private PreparedStatement insertStatement = null;
  private PreparedStatement deleteStatement = null;
  private PreparedStatement findByIdStatement = null;
  private PreparedStatement findAllStatement = null;

  private PreparedStatement findByTerminIdStatement = null;

  SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm");

  private static final String SQL_INSERT = "INSERT INTO terminplaner.adressen(timestamp,TerminID,ort,plz,strasse,land)"
      + "VALUES(? ,?, ?, ?, ?, ?)";

  private static final String SQL_UPDATE = "UPDATE terminplaner.adressen "
      + "SET timestamp=?, TerminID=?, ort=?, plz=?, strasse=?, land=? " + "WHERE id=?";

  private static final String SQL_SELECT_BY_ID = "SELECT id, timestamp, ort, plz, strasse, land, TerminID "
      + "FROM terminplaner.adressen " + "WHERE id=?";

  private static final String SQL_SELECT_ALL = "SELECT id, timestamp, ort, plz, strasse, land, TerminID "
      + "FROM terminplaner.adressen";

  private static final String SQL_DELETE_BY_ID = "DELETE FROM terminplaner.adressen " + "WHERE id=?";

  private static final String SQL_SELECT_BY_TERMINID = "SELECT id, timestamp, ort, plz, strasse, land, TerminID "
      + "FROM terminplaner.adressen " + "WHERE TerminID=?";

  @Override
  public int save(Connection con, Adresse data) throws IOException {
    if (con == null)
      throw new IllegalArgumentException("Connection darf nicht null sein!");
    if (data == null)
      throw new IllegalArgumentException("Daten duerfen nicht null sein!");

    int changed = 0;

    if (data.neu())
      changed = insert(con, data, new Date());
    else if (!data.neu())
      changed = update(con, data, new Date());

    return changed;
  }

  private int insert(Connection con, Adresse data, Date timestamp) throws IOException {
    if (timestamp == null)
      throw new IllegalArgumentException("timestamp darf nicht null sein!");

    int recordsAffected = 0;
    try {
        insertStatement = con.prepareStatement(SQL_INSERT, PreparedStatement.RETURN_GENERATED_KEYS);

      insertStatement.setTimestamp(1, JdbcDao.toSqlTimestamp(timestamp));

      if (data.getTermin() != null)
        insertStatement.setLong(2, data.getTermin().getId());
      else
        insertStatement.setLong(2, new Long(-999));

      insertStatement.setString(3, data.getOrt());
      insertStatement.setString(4, data.getPlz());
      insertStatement.setString(5, data.getStrasse());
      insertStatement.setString(6, data.getLand());

      recordsAffected = insertStatement.executeUpdate();
      ResultSet rs = insertStatement.getGeneratedKeys();
      
      while (rs.next()) {
        data.setID(rs.getLong(1));
        data.setTimestamp((Date) timestamp.clone());
      }
      
    } catch (SQLException e) {
      throw new IOException("Fehler bei InsertStatement!", e);
    }
    return recordsAffected;
  }

  private int update(Connection con, Adresse data, Date timestamp) throws IOException {
    if (timestamp == null)
      throw new IllegalArgumentException("timestamp darf nicht null sein!");

    int recordsAffected = 0;
    try {
        updateStatement = con.prepareStatement(SQL_UPDATE);
      

      updateStatement.setTimestamp(1, JdbcDao.toSqlTimestamp(timestamp));
      
      if (data.getTermin() != null)
        updateStatement.setLong(2, data.getTermin().getId());
      else
        insertStatement.setLong(2, new Long(-999));
      
      updateStatement.setString(3, data.getOrt());
      updateStatement.setString(4, data.getPlz());
      updateStatement.setString(5, data.getStrasse());
      updateStatement.setString(6, data.getLand());

      updateStatement.setLong(7, data.getId());

      recordsAffected = updateStatement.executeUpdate();
      data.setTimestamp((Date) timestamp.clone());

    } catch (SQLException e) {
      throw new IOException("Fehler bei UpdateStatement!", e);
    }
    
    return recordsAffected;
  }

  @Override
  public int delete(Connection con, Adresse data) throws IOException {
    if (con == null)
      throw new IllegalArgumentException("Connection darf nicht null sein!");
    if (data == null)
      throw new IllegalArgumentException("Daten duerfen nicht null sein!");

    int recordsAffected = 0;
    try {
        deleteStatement = con.prepareStatement(SQL_DELETE_BY_ID);
      
      if (data.getId() == null)
        throw new IllegalArgumentException("Adresse befindet sich nicht in der Datenbank!");
      
      deleteStatement.setLong(1, data.getId());

      recordsAffected = deleteStatement.executeUpdate();
      data.idLoeschen();
      
    } catch (SQLException e) {
      throw new IOException("Fehler bei DeleteStatement", e);
    }
    return recordsAffected;

  }

  @Override
  public Adresse findById(Connection con, Long id) throws IOException {
    if (con == null)
      throw new IllegalArgumentException("Connection darf nicht null sein!");
    if (id == null)
      throw new IllegalArgumentException("ID darf nicht null sein");

    Adresse adresse = null;
    try {
        findByIdStatement = con.prepareStatement(SQL_SELECT_BY_ID);
      findByIdStatement.setLong(1, id);

      ResultSet rs = findByIdStatement.executeQuery();
      rs.next();

      adresse = createAdresseFromResultSet(con, rs);

      if (!rs.isLast())
        throw new IOException("Fehler in der Datenstruktur, id darf jeweils nur einmal vorkommen!");

    } catch (SQLException e) {
      throw new IOException("Fehler bei findByID Statement!", e);
    }
    if (adresse == null)
      throw new IOException("Adresse mit dieser ID nicht gefunden!");

    return adresse;
  }

  @Override
  public ArrayList<Adresse> findAll(Connection con) throws IOException {
    if (con == null)
      throw new IllegalArgumentException("Connection darf nicht null sein!");

    ArrayList<Adresse> adressen = new ArrayList<Adresse>();
    Adresse adresse = null;

    try {
        findAllStatement = con.prepareStatement(SQL_SELECT_ALL);

      ResultSet rs = findAllStatement.executeQuery();
      while (rs.next()) {

        adresse = createAdresseFromResultSet(con, rs);
        adressen.add(adresse);

      }

    } catch (SQLException e) {
      throw new IOException("Fehler bei findAll Statement!", e);
    }

    return adressen;
  }

  public Adresse findByTerminId(Connection con, Long terminId) throws IOException {
    if (con == null)
      throw new IllegalArgumentException("Connection darf nicht null sein!");
    if (terminId == null)
      throw new IllegalArgumentException("TerminID darf nicht null sein");

    Adresse adresse = null;

    try {
        findByTerminIdStatement = con.prepareStatement(SQL_SELECT_BY_TERMINID);

      findByTerminIdStatement.setLong(1, terminId);

      ResultSet rs = findByTerminIdStatement.executeQuery();

      rs.next();

      adresse = createAdresseFromResultSet(con, rs);

      if (!rs.isLast())
        throw new IOException(
            "Fehler in Datenstruktur, TerminID darf in Tabelle adressen nur jeweils einmal vorkommen!");

    } catch (SQLException e) {
      throw new IOException("Fehler bei findByTerminId Statement!", e);
    }
    if (adresse == null)
      throw new IOException("Adresse mit dieser TerminID nicht gefunden!");

    return adresse;
  }

  private Adresse createAdresseFromResultSet(Connection con, ResultSet rs) throws SQLException, IOException {

    Adresse adresse = null;

    Long id = new Long(rs.getInt("id"));
    String ort = rs.getString("ort");
    String plz = rs.getString("plz");
    String strasse = rs.getString("strasse");
    String land = rs.getString("land");

    adresse = new Adresse(ort, plz, strasse, land);
    adresse.setID(id);
    adresse.setTimestamp(new Date());
    
    return adresse;

  }
}
